/******************************************************************************
 * @File name   :      MaintainCarPlateDaoImpl.java
 *
 * @Author      :      ANMA
 *
 * @Date        :      Jul 21, 2015
 *
 * @Copyright Notice: 
 * Copyright (c) 2014 DMS, Inc. All  Rights Reserved.
 * This software is published under the terms of the DMS Software
 * License version 1.0, a copy of which has been included with this
 * distribution in the LICENSE.txt file.
 * 
 * ----------------------------------------------------------------------------
 * Date                         Who         Version         Comments
 * Jul 21, 2015 5:20:03 PM    ANMA      1.0            Initial Version
 *****************************************************************************/
package com.cap.bts.crmdp.dealer.member.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Inject;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.commonfunc.common.util.UserUtil;
import com.cap.bts.crmdp.dealer.member.dao.IMaintainCarPlateDao;
import com.cap.bts.crmdp.dealer.member.domain.CarPlateInfo;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;
import com.cap.bts.framework.common.util.Utils;
import com.cap.bts.framework.common.util.WhereConditionGenerator;

/**
 * 
 */
@Repository("maintainCarPlateDao")
public class MaintainCarPlateDaoImpl extends BaseDaoImpl<CarPlateInfo, Long> implements IMaintainCarPlateDao {
	
	@Inject
	public MaintainCarPlateDaoImpl(@Value("com.cap.bts.crmdp.dealer.member.domain.CarPlateInfo") Class<CarPlateInfo> clazz) {
		super(clazz);
	}

	@Override
	public List<CarPlateInfo> getCarPlateInfoListByCarPlateOrMemberId(String carPlate, Long memberId) throws AppException {
		String hql = "select cinfo ";
		String fromStr = " from CarPlateInfo cinfo ";
		String whereStr = " where 1=1 ";
		String orderHql = " order by cinfo.id desc ";
		StringBuilder sqlStr = new StringBuilder();

		WhereConditionGenerator condition = new WhereConditionGenerator(whereStr);

		if (Utils.notEmpty(carPlate)){
			condition.andLike("cinfo.carPlate", "%", carPlate, "%");
		}
		if (Utils.notNullAndZero(memberId)){
			condition.and("cinfo.memberId", "=", memberId);
		}

		sqlStr.append(hql);	
		sqlStr.append(fromStr);	
		sqlStr.append(condition.toString());	
		sqlStr.append(orderHql);	

		return getListByQuery(sqlStr.toString()); 
	}

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年8月12日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainCarPlateDao#getUsingPackageCarPlateId(java.lang.String)
    **/
    @Override
    public int getCountUsingPackageCarPlateId(Long id) throws AppException {
        String hql = "select count(1) from tm_raw_car_plate c,tm_member_package_info p where c.ID = p.ACTIVE_CAR_ID "
            + "and c.MEMBER_ID = p.ACTIVE_MEMBER_ID and c.ID =" + id;
        return this.countByNativeQuery(hql);
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年8月17日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainCarPlateDao#getCarPlateInfoListByCarPlateAndMemberId(java.lang.String, java.lang.Long)
    **/
    @Override
    public List<CarPlateInfo> getCarPlateInfoListByCarPlateAndMemberId(String carPlate,
        Long memberId) throws AppException {
        Map<String,Object> paraMap = new HashMap<String,Object>();
        paraMap.put("carPlate", carPlate);
        paraMap.put("memberId", memberId);
        String hql = "from CarPlateInfo cinfo where cinfo.carPlate=:carPlate and cinfo.memberId=:memberId";
        return this.getListByQueryWithMap(hql, paraMap);
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年8月25日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainCarPlateDao#getCarPlateListWithPackage(java.lang.Long, java.lang.String, java.lang.Long)
    **/
    @Override
    public List<CarPlateInfo> getCarPlateListWithPackage(Long dealerId, Long carPlateId, Long memberId)
        throws AppException {
        String hql = "select * from tm_raw_car_plate cinfo where cinfo.ID = "
            + "(select a.ACTIVE_CAR_ID from ("
            + " select packageInfo.ACTIVE_DEALER_ID , packageInfo.ACTIVE_MEMBER_ID , packageInfo.ACTIVE_CAR_ID"
            + " from tm_member_package_info packageInfo "
            + " where packageInfo.ACTIVE_DEALER_ID=:dealerId "
            + " and packageInfo.ACTIVE_MEMBER_ID=:memberId "
            + " and packageInfo.ACTIVE_CAR_ID=:carPlateId "
            + " group by packageInfo.ACTIVE_DEALER_ID "
            + " , packageInfo.ACTIVE_MEMBER_ID "
            + " , packageInfo.ACTIVE_CAR_ID) a)";
        Map<String,Object> paraMap = new HashMap<String,Object>();
        paraMap.put("dealerId", dealerId);
        paraMap.put("memberId", memberId);
        paraMap.put("carPlateId", carPlateId);
        return this.getListByNativeQueryWithMap(hql, paraMap);
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年9月6日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainCarPlateDao#updateCarPlateByCarPlateInfo(com.cap.bts.crmdp.dealer.member.domain.CarPlateInfo)
    **/
    @Override
    public void updateCarPlateByCarPlateInfo(CarPlateInfo carPlateInfo, Long newMemberId) throws AppException {
        try {
            String hql = "update CarPlateInfo c set c.memberId="+newMemberId+" "
                + "where c.id='"+carPlateInfo.getId()+"'";
            this.updateByQuery(hql);
        } catch (Exception e) {
            throw new AppException("updateCarPlateByCarPlateInfo", e);
        }
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年9月6日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainCarPlateDao#updateCarPlateForMember(java.lang.Long, java.lang.String)
    **/
    @Override
    public void updateCarPlateForMember(Long id, String carPlate, int updateTimes) throws AppException {
        try {
            String hql = "update CarPlateInfo c set c.carPlate='"+carPlate+"',c.updateTimes="+updateTimes
                + " where c.id="+id;
            this.updateByQuery(hql);
        } catch (Exception e) {
            throw new AppException("updateCarPlateForMember", e);
        }
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年9月6日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainCarPlateDao#getCarPlateInfoById(java.lang.Long)
    **/
    @Override
    public CarPlateInfo getCarPlateInfoById(Long id) throws AppException {
        try {
            return this.findById(id);
        } catch (Exception e) {
            throw new AppException("updateCarPlateForMember", e);
        }
    }

    @Override
    public CarPlateInfo getCarPlateInfoByMarsInfo(String marsCarId, Long memberId) {
        Map<String,Object> paraMap = new HashMap<String,Object>();
        paraMap.put("marsCarId", marsCarId);
        paraMap.put("memberId", memberId);
        String hql = "from CarPlateInfo cinfo where cinfo.marsCarId=:marsCarId and cinfo.memberId=:memberId";
        List<CarPlateInfo> carPlateInfoList = getListByQueryWithMap(hql, paraMap);
        if(carPlateInfoList != null && !carPlateInfoList.isEmpty()){
            return carPlateInfoList.get(0);
        }else{
            return null;
        }
        
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年9月28日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainCarPlateDao#updateCarPlate(com.cap.bts.crmdp.dealer.member.domain.CarPlateInfo, java.lang.Long)
    **/
    @Override
    public void updateCarPlate(CarPlateInfo carPlateInfo) throws AppException {
        try {
            String hql = "update CarPlateInfo c set c.carPlate='"+carPlateInfo.getCarPlate()+"' "
                + "where c.id='"+carPlateInfo.getId()+"'";
            this.updateByQuery(hql);
        } catch (Exception e) {
            throw new AppException("updateCarPlate", e);
        }
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年10月15日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainCarPlateDao#getCarPlateInfoListByCarPlate(java.lang.String)
    **/
    @Override
    public List<CarPlateInfo> getCarPlateInfoListByCarPlate(String carPlate) throws AppException {
        String sql = "select c.* from tm_raw_car_plate c, tm_raw_member m"
            + " where c.MEMBER_ID = m.ID"
            + " and m.DEALER_ID = " + UserUtil.getCurrentUser().getDealerId()
            + " and c.CAR_PLATE = '" + carPlate + "'";
        return this.getListByNativeQuery(sql);
    }
    
    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年10月15日
     * @see com.cap.bts.crmdp.dealer.member.dao.IMaintainCarPlateDao#getCarPlateInfoListByCarPlate(java.lang.String)
     **/
    @Override
    public List<CarPlateInfo> getCarPlateInfoListByCarPlateForMars(Long dearId, String carMarsId) throws AppException {
    	String sql = "select c.* from tm_raw_car_plate c, tm_raw_member m"
    			+ " where c.MEMBER_ID = m.ID"
    			+ " and m.DEALER_ID = " + dearId
    			+ " and c.MARS_CAR_ID = '" + carMarsId + "'";
    	return this.getListByNativeQuery(sql);
    }

	@Override
	public CarPlateInfo getCarPlateInfoByMarsCarId(String carId) {
		String hql = "from CarPlateInfo where marsCarId = :carId";
		
		Map paramMap = new HashMap();
		
		paramMap.put("carId", carId);
		
		List<CarPlateInfo> list = this.getListByQueryWithMap(hql, paramMap);
		
		if(!list.isEmpty()){
			return list.get(0);
		}
		return null;
	}
	
	@Override
	public CarPlateInfo getCarPlateInfoByMarsCarIdAndMemberId(String carId, Long memberId) {
		String hql = "from CarPlateInfo where marsCarId = :carId and memberId= :memberId";
		
		Map paramMap = new HashMap();
		
		paramMap.put("carId", carId);
		paramMap.put("memberId", memberId);
		
		List<CarPlateInfo> list = this.getListByQueryWithMap(hql, paramMap);
		
		if(!list.isEmpty()){
			return list.get(0);
		}
		return null;
	}
	
	
	@Override
	public CarPlateInfo getCarPlateInfoByMarsCarIdAndMarsMemberId(String marsCarId, String marsMemberId) {
		String sql = "SELECT a.* FROM	tm_raw_car_plate a,	tm_raw_member b WHERE a.MEMBER_ID = b.id "
				+ " AND a.MARS_CAR_ID =:carId AND b.MARS_MEMBER_ID = :marsMemberId";
		
		Map paramMap = new HashMap();
		
		paramMap.put("carId", marsCarId);
		paramMap.put("marsMemberId", marsMemberId);
		
		List<CarPlateInfo> list = this.getListByNativeQueryWithMap(sql, paramMap);
		
		if(!list.isEmpty()){
			return list.get(0);
		}
		return null;
	}
}